package es.geodata.gvsig.extLinkedTables;


import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Properties;
import java.util.Vector;

import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JComponent;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableModel;

import org.apache.log4j.Logger;

import com.hardcode.gdbms.driver.exceptions.ReadDriverException;
import com.hardcode.gdbms.engine.values.Value;
import com.hardcode.gdbms.engine.values.ValueWriter;
import com.iver.andami.PluginServices;
import com.iver.cit.gvsig.FiltroExtension;
import com.iver.cit.gvsig.fmap.layers.FLyrVect;
import com.iver.cit.gvsig.fmap.layers.ReadableVectorial;
import com.iver.cit.gvsig.fmap.layers.SelectableDataSource;
import com.iver.cit.gvsig.fmap.layers.VectorialDBAdapter;
import com.iver.cit.gvsig.fmap.layers.VectorialFileAdapter;
import com.iver.cit.gvsig.fmap.layers.layerOperations.AlphanumericData;
import com.iver.cit.gvsig.project.documents.table.ProjectTable;
import com.iver.utiles.extensionPoints.ExtensionPoints;
import com.iver.utiles.extensionPoints.ExtensionPointsSingleton;
import com.jeta.forms.components.panel.FormPanel;
import com.jeta.forms.gui.common.FormException;

import es.icarto.gvsig.navtableforms.AbstractForm;
import es.udc.cartolab.gvsig.navtable.AbstractNavTable;
import es.udc.cartolab.gvsig.navtable.preferences.Preferences;


public class LinkedTablesForm extends AbstractForm{

	private static final long serialVersionUID = 1L;

	private FLyrVect layer;
	private ProjectTable ptMain;
	private ProjectTable ptLinked;
	private Properties properties;
	private String linkedName;
	private String dir;

	private FormPanel form;
	private JTable table;
	private JTable tree;
	private SelectableDataSource sds;
	private SelectableDataSource linkedsds;
	private String colNamesMain[];
	private int fieldIndex;
	private Connection connection;
	private JButton filterLinked = null;
	
	
	public LinkedTablesForm(FLyrVect layer, ProjectTable projectTable, ProjectTable linkedProjectTable, Properties prop) {
		
		super(layer);
		this.layer = layer;
		this.ptMain = projectTable;
		this.ptLinked = linkedProjectTable;
		this.properties = prop;
		this.linkedName = ptLinked.getName();
		if (linkedName.lastIndexOf(".") > 0){
			this.linkedName = linkedName.substring(0, linkedName.lastIndexOf("."));
		}
		this.dir = getLayerDir(layer);
		initWindow();
		
	}	
	
	
	// Initialize Window components
	private void initWindow() {
		
		// Set window width and height
		int height = Integer.parseInt(properties.getProperty("WINDOW_HEIGHT", "600").trim());		
		int width = Integer.parseInt(properties.getProperty("WINDOW_WIDTH", "900").trim());
		viewInfo.setHeight(height);
		viewInfo.setWidth(width);
		viewInfo.setTitle(PluginServices.getText(LinkedTablesPreferences.CONFIG_PLUGIN, "window_title"));

        // Get screen size
        Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
		viewInfo.setX(d.width / 2 - width / 2);
		viewInfo.setY(100);
		
		// Set labels
		form.getLabel("lbl_main").setText(PluginServices.getText(this, "lbl_main"));
		form.getLabel("lbl_linked").setText(PluginServices.getText(this, "lbl_linked"));	
		form.getLabel("lbl_version").setText(PluginServices.getText(this, "lbl_version"));	
		
	}

	
	// Initialize Main Table
	private void initMainTable() throws ReadDriverException {
		
		File file = getFileAlias(layer);
		sds = getRecordset();
		if (ptLinked != null){
			linkedsds = getLinkedRecordset();					
		}
		String fieldName = ptMain.getField1();
		fieldIndex = sds.getFieldIndexByName(fieldName);
		
		// Disable auto resizing
		table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

		// Set font
		Font font = new Font("Tahoma", Font.PLAIN, 10);
		table.getTableHeader().setFont(font);
		
		// Get column fields
		colNamesMain = sds.getFieldNames();
		TableModel dataModel = new DefaultTableModel(1, colNamesMain.length);
		table.setModel(dataModel);
		
		// Set column headers. 
		TableColumnModel columnModel = table.getColumnModel();
		String alias;
		for (int i = 0; i < colNamesMain.length; i++) {
			alias = getAlias(file, colNamesMain[i]);
			columnModel.getColumn(i).setHeaderValue(alias);
		}		
		
	}

	
	// Get Alias File of main table (it has to be a shape file)
	private File getFileAlias(FLyrVect layer){
		
		File layerFile = null;
		String filePath = null;
		String pathToken = null;
		File fileAlias = null;		
		
		ReadableVectorial source = layer.getSource();

	    if (source != null && source instanceof VectorialFileAdapter) {
			layerFile = ((VectorialFileAdapter) source).getFile();
			filePath = layerFile.getAbsolutePath();
			pathToken = filePath.substring(0, filePath.lastIndexOf("."));
			fileAlias = new File(pathToken + ".alias");
			if (!fileAlias.exists()) {
			    pathToken = Preferences.getAliasDir() + File.separator + layer.getName();
				pathToken = pathToken.substring(0, pathToken.lastIndexOf("."));	    				    
			    fileAlias = new File(pathToken + ".alias");
			}
	    } 
	    else if (source instanceof VectorialDBAdapter) {
	    	pathToken = Preferences.getAliasDir() + File.separator + layer.getName();
			pathToken = pathToken.substring(0, pathToken.lastIndexOf("."));	    	
	    	fileAlias = new File(pathToken + ".alias");
	    } 

	    return fileAlias;
		
	}
	
	
	// Get Alias of linked table (it has to be a dbf file)
	private File getFileAlias(String dirPath, String fileName){
		
		String aux = null;
		File fileAlias = new File(dirPath + File.separator + fileName + ".alias");
		if (!fileAlias.exists()) {
			aux = Preferences.getAliasDir() + File.separator + fileName;
			aux = aux.substring(0, aux.lastIndexOf("."));	    				    
		    fileAlias = new File(aux + ".alias");
		}

	    return fileAlias;
		
	}	
	
	
	// Get Alias of selected field in the specified Alias file
	private String getAlias(File file, String fieldName){
		
		String alias = fieldName;
		if (file.exists()) {
			try {
				String line;
				BufferedReader fileReader = new BufferedReader(new FileReader(file));
				while ((line = fileReader.readLine()) != null) {
					String tokens[] = line.split("=");
					if (tokens.length == 2) {
						String attrName = tokens[0].toUpperCase();
						if (fieldName.toUpperCase().compareTo(attrName) == 0) {
							alias = tokens[1];
							break;
						}
					}
				}
			} catch (FileNotFoundException e) {
				AbstractNavTable.logger.error(e.getMessage(), e);
			} catch (IOException e) {
				AbstractNavTable.logger.error(e.getMessage(), e);
			}
		}
		return alias;		
		
	}
	
	
	// Initialize Linked Table
	private void initLinkedTable() throws ReadDriverException {

		try {
			String dbfConn = properties.getProperty("DBF_CONN");			
			//String dbfDir = properties.getProperty("DBF_DIR");			
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	        String connString = "jdbc:odbc:Driver=" + dbfConn + ";DefaultDir=" + dir;
	        connection = DriverManager.getConnection(connString);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			LinkedTablesExtension.showError("error_dbf_dir", e.getMessage(), "app_title");				
		}
		
	}
	
	
	// Retrieve Main Table data into JTable
	private void setMainTable() throws ReadDriverException {

		// Set row data
		Value[] row = sds.getRow(getPosition());
		for (int j = 0; j < colNamesMain.length; j++) {
			table.setValueAt(row[j], 0, j);
		}
		
	}

	
	// Retrieve Linked Table data into JTable
	private void setLinkedTable() throws ReadDriverException {

		String fieldValue = sds.getFieldValue(getPosition(), fieldIndex).getStringValue(ValueWriter.internalValueWriter);
		String sql = "";
		
        try {

    		//String dbfColumns = properties.getProperty("DBF_COLUMNS");		 
    		File file = getFileAlias(dir, linkedName);        	
			String fieldName2 = ptMain.getField2();	        
			//sql = "SELECT " + dbfColumns + " FROM [" + linkedName + "] WHERE " + fieldName2 + " = " + fieldValue + ";";
			sql = "SELECT * FROM [" + linkedName + "] WHERE " + fieldName2 + " = " + fieldValue + ";";
	        System.out.println(sql);	   
	        Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);		        
	        ResultSet rs = stmt.executeQuery(sql);
	        ResultSetMetaData rsmd = rs.getMetaData();

			// Get total rows and columns
	        rs.last();
	        int rcount = rs.getRow();	        
	        System.out.println(rcount);
	        int numColumns = rsmd.getColumnCount();
			TableModel dataModel = new DefaultTableModel(rcount, numColumns);
			tree.setModel(dataModel);
	        
			// Set column headers
			TableColumnModel columnModel = tree.getColumnModel();
			String alias;
			for (int i=1; i<=numColumns; i++) {
				alias = getAlias(file, rsmd.getColumnName(i));
				columnModel.getColumn(i-1).setHeaderValue(alias);	            
	        }

			// Set row data
			rs.first();
			for (int i=0; i<rcount; i++) {
				for (int j=1; j<=numColumns; j++) {
					tree.setValueAt(rs.getString(j), i, j-1);
				}
				rs.next();				
			}			
			
        }
        catch (SQLException e) {
			e.printStackTrace();
			LinkedTablesExtension.showError("error_query", sql + "\n" + e.getMessage(), "app_title");			
		}

	}

	
	// Get Recordset associated with Linked Table
	private SelectableDataSource getLinkedRecordset() {
		try {
			if (linkedsds == null && ptLinked != null){
				AlphanumericData ad;
				ad = ptLinked.getAssociatedTable();
				if (ad != null){
					linkedsds = ad.getRecordset();
				}
				else{
					linkedsds = ptLinked.getModelo().getRecordset();
				}
			}
		} catch (ReadDriverException e) {
			e.printStackTrace();
		}
		return linkedsds;
	}
	
	
	// Get Layer directory
	private String getLayerDir(FLyrVect layerV){
		ReadableVectorial rv = layerV.getSource();
		if (rv instanceof VectorialFileAdapter){
			VectorialFileAdapter vfa = (VectorialFileAdapter) rv;
			File file = vfa.getFile();
			System.out.println(file.getAbsolutePath());
			return file.getParent();
		}		
		return null;
	}


	// Get Icon
    private JLabel getIcon(File iconPath) {
		ImageIcon logo = new ImageIcon(iconPath.getAbsolutePath());
		JLabel icon = new JLabel();
		icon.setIcon(logo);
		return icon;
    }
    
    
    // Get first row of NorthPanel to add header image
    private JPanel getNorthFirstRow() {
		File iconPath = getHeaderFile();
		JPanel northFirstRow = new JPanel(new BorderLayout());
		if (iconPath != null && iconPath.exists()) {
		    northFirstRow.setBackground(Color.WHITE);
		    northFirstRow.add(getIcon(iconPath), BorderLayout.WEST);
		}
		return northFirstRow;
    }		

    
    
	@Override
	public FormPanel getFormBody() {
		if (form == null) {
			try {
				FileInputStream fis = new FileInputStream(new File(getXMLPath()));
				form = new FormPanel(fis);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (FormException e) {
				e.printStackTrace();
			}
		}
		return form;
	}

	
	@Override
	public String getXMLPath() {
		return LinkedTablesPreferences.XMLDATAFILE_PATH;
	}

	
	@Override
	public Logger getLoggerName() {
		return Logger.getLogger("LinkedTablesForm");
	}

	
	@Override
	protected void fillSpecificValues() {
        if (connection != null){	
        	try {
	        	setMainTable();
	        	setLinkedTable();
        	} catch (ReadDriverException rde) {
        		rde.printStackTrace();
        	}
        }
	}    
    
	
	@Override
	protected void setListeners() {

		HashMap<String, JComponent> widgets = getWidgetComponents();
		super.setListeners();
		if (widgets.containsKey("table")){
			table = (JTable) widgets.get("table");
		}
		if (widgets.containsKey("treetable")){
			tree = (JTable) widgets.get("treetable");
		}
		try {
			initMainTable();
			initLinkedTable();		
		} catch (ReadDriverException e) {
			e.printStackTrace();
		}
		
	}

	
	@Override
	protected void removeListeners() {
		super.removeListeners();
	}

	
	@Override
	protected Vector<?> checkChangedValues() {
		return null;
	}
	

	@Override
	protected void saveRegister() {

	}

	
	@Override   
    protected void registerNavTableButtonsOnActionToolBarExtensionPoint(){

    	ExtensionPoints extensionPoints = ExtensionPointsSingleton.getInstance();
    	
    	filterB = getNavTableButton(filterB, "/filter.png", "filterTooltip");
    	extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-enable-filter", filterB);

    	noFilterB = getNavTableButton(noFilterB, "/nofilter.png", "noFilterTooltip");
    	extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-disable-filter", noFilterB);    	

    	filterLinked = getNavTableButton(filterLinked, "/filter.png", "filterLinked");
    	extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-enable-filter-linked", filterLinked);    	
    	filterLinked.setActionCommand("filter_linked");
    	
    	zoomB = getNavTableButton(zoomB, "/zoom.png", "zoomButtonTooltip");
    	extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-zoom", zoomB);

    	selectionB = getNavTableButton(selectionB, "/Select.png", "selectionButtonTooltip");
    	extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-selection", selectionB);
		
    }

    
	@Override	
    protected JPanel getNorthPanel() {
		if (northPanel == null) {
		    initNorthPanelButtons();
		    northPanel = new JPanel(new BorderLayout());
			northPanel.add(getNorthFirstRow(), BorderLayout.NORTH);		    
		    northPanel.add(getOptionsPanel(), BorderLayout.SOUTH);
		}
		return northPanel;
    }

    
	@Override
	public boolean isSavingValues() {
		return false;
	}
       
	
	@Override
    public void actionPerformed(ActionEvent e) {
	
		if (e.getActionCommand().equals("filter_linked")){
		    FiltroExtension fe = new FiltroExtension();
		    fe.initialize();
		    fe.setDatasource(getLinkedRecordset(), linkedName);
		    fe.execute("FILTER_DATASOURCE");		
			super.setOnlySelected(true);		    
		}
		else{
			super.actionPerformed(e);
		}		
		
    }
	

	@Override
	public SelectableDataSource getRecordset() {
		try {
			if (sds == null){
				sds = ptMain.getAssociatedTable().getRecordset();				
			}
		} catch (ReadDriverException e) {
			e.printStackTrace();
		}
		return sds;
	}
	
    
}